﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Xml;
using MySql.Data;
using MySql.Data.MySqlClient;
using System.Data;
using System.Configuration;
using System.Security.Cryptography;
using System.Data.SqlTypes;

namespace WebServices
{
    public class DbAccess
    {
        // Connection information:
        private string dbAddress  = ConfigurationManager.AppSettings["address"];
        private string dbUserName = ConfigurationManager.AppSettings["dbUserName"];
        private string dbPassword = ConfigurationManager.AppSettings["dbPassword"];
        private string dbDatabase = ConfigurationManager.AppSettings["dbDatabase"];
        private Random random = new Random();

        // The connection itself
        //private MySqlConnection conn = null;

        // Constructor. If inDevEnvironment then we redirect the dbAddress to
        // localhost.
        public DbAccess(bool inDevEnvironment = false)
        {
            if (inDevEnvironment)
            {
                dbAddress = "localhost";
            }
        }
        public XmlDocument GetCategories()
        {
            string toReturn = "<Categories>";

            // Open the database connection.
            using (MySqlConnection conn = this.Open())
            {

                // Create the command object.
                MySqlCommand command = new MySqlCommand("CALL Mesh_GetCategories();", conn);

                // Execute the command object
                MySqlDataReader reader = command.ExecuteReader();

                //// Pull data out of the command object, one column at a time.
                while (reader.Read())
                {
                    // Append data to the return string.
                    toReturn += "<Category>" +
                                    "<Title>" + reader[0] + "</Title>" +
                                    "<Description>" + reader[1] + "</Description>" +
                                "</Category>";
                }

                toReturn += "</Categories>";



                //// Since we're done with it, close the reader.
                reader.Close();

                // Close the database connection.
                conn.Close();
            }
            XmlDocument xmlNewDoc = new XmlDocument();
            xmlNewDoc.LoadXml(toReturn);
            return xmlNewDoc;
        }
        public XmlDocument GetUserInfoByUID(string uid)
        {
            string toReturn = "<UserInfos>";

            // Open the database connection.
            using (MySqlConnection conn = this.Open())
            {

                // Create the command object.
                MySqlCommand command = new MySqlCommand("CALL Mesh_GetUserInfoByUID(@uid);", conn);
                command.Parameters.Add(new MySqlParameter("uid", uid));

                // Execute the command object
                MySqlDataReader reader = command.ExecuteReader();

                // Pull data out of the command object, one column at a time.
                while (reader.Read())
                {
                    // Append data to the return string.
                    toReturn += "<UserInfo>" +
                                    "<uid>" + reader[0] + "</uid>" +
                                    "<FirstName>" + reader[1] + "</FirstName>" +
                                    "<LastName>" + reader[2] + "</LastName>" +
                                    "<Reputation>" + reader[3] + "</Reputation>" +
                                    "<Birthday>" + reader[4] + "</Birthday>" +
                                "</UserInfo>";
                }

                toReturn += "</UserInfos>";



                // Since we're done with it, close the reader.
                reader.Close();

                // Close the database connection.
                conn.Close();
            }

            
            XmlDocument xmlNewDoc = new XmlDocument();
            xmlNewDoc.LoadXml(toReturn);
            return xmlNewDoc;
        }
        /*public XmlDocument GetUserPostByCat(string categoryType)
        {
            string toReturn = "<UserPosts>";

            // Open the database connection.
            using (MySqlConnection conn = this.Open())
            {
                // Create the command object.
                MySqlCommand command = new MySqlCommand("CALL Mesh_GetUserPostByCat(@categoryType);", conn);
                command.Parameters.Add(new MySqlParameter("categoryType", categoryType));

                // Execute the command object
                MySqlDataReader reader = command.ExecuteReader();

                // Pull data out of the command object, one column at a time.
                while (reader.Read())
                {
                    // Append data to the return string.
                    toReturn += "<UserPost>" +
                                    "<UserNo>" + reader[8] + "</UserNo>" +
                                    "<Post>" +
                                        "<Id>" + reader[0] + "</Id>" +
                                        "<Title>" + reader[1] + "</Title>" +
                                        "<Date>" + reader[2] + "</Date>" +
                                        "<Link>" + reader[3] + "</Link>" +
                                        "<ImgURL>" + reader[4] + "</ImgURL>" +
                                        "<PopValue>" + reader[5] + "</PopValue>" +
                                        "<Description>" + reader[6] + "</Description>" +
                                        "<ZipLocale>" + reader[7] + "</ZipLocale>" +
                                        "<CategoryTitle>" + reader[9] + "</CategoryTitle>" +
                                    "</Post>" +
                                    "<User>" +
                                        "<UserID>" + reader[11] + "</UserID>" +
                                        "<UserFirstName>" + reader[12] + "</UserFirstName>" +
                                        "<UserLastName>" + reader[13] + "</UserLastName>" +
                                        "<UserReputation>" + reader[14] + "</UserReputation>" +
                                        "<UserBirthDate>" + reader[15] + "</UserBirthDate>" +
                                        "<UserLocale>" + reader[16] + "</UserLocale>" +
                                    "</User>" +
                                "</UserPost>";
                }

                toReturn += "</UserPosts>";

                // Since we're done with it, close the reader.
                reader.Close();

                // Close the database connection.
                conn.Close();
            }
            XmlDocument xmlNewDoc = new XmlDocument();
            xmlNewDoc.LoadXml(toReturn);
            return xmlNewDoc;
        }*/
        public XmlDocument GetUserPostByZip(string zip)
        {
            //string toReturn = "<UserPosts>";
            XmlDocument doc = new XmlDocument();
            //XmlNode docNode = doc.CreateXmlDeclaration("1.0", "UTF-8", null);
            //doc.AppendChild(docNode);
            XmlNode userPostsNode = doc.CreateElement("UserPosts");
            doc.AppendChild(userPostsNode);

            // Open the database connection.
            using (MySqlConnection conn = this.Open())
            {

                // Create the command object.
                MySqlCommand command = new MySqlCommand("CALL Mesh_GetUserPostByZip(@zip);", conn);
                command.Parameters.Add(new MySqlParameter("zip", zip));

                // Execute the command object
                MySqlDataReader reader = command.ExecuteReader();

                // Pull data out of the command object, one column at a time.
                while (reader.Read())
                {
                    XmlNode userPostNode = doc.CreateElement("UserPost");
                    userPostsNode.AppendChild(userPostNode);

                    XmlNode userNoNode = doc.CreateElement("UserNo");
                    userNoNode.InnerText = reader.GetString("UserID");
                    userPostNode.AppendChild(userNoNode);

                    XmlNode postNode = doc.CreateElement("Post");
                    userPostNode.AppendChild(postNode);

                    XmlNode postIdNode = doc.CreateElement("Id");
                    postIdNode.InnerText = reader.GetString("Post_ID");
                    postNode.AppendChild(postIdNode);

                    XmlNode postTitleNode = doc.CreateElement("Title");
                    postTitleNode.InnerText = reader.GetString("Post_Title");
                    postNode.AppendChild(postTitleNode);

                    XmlNode postDateNode = doc.CreateElement("Date");
                    postDateNode.InnerText = reader.GetString("Post_Date");
                    postNode.AppendChild(postDateNode);

                    try
                    {
                        XmlNode postLinkNode = doc.CreateElement("Link");
                        postLinkNode.InnerText = reader.GetString("Post_Link");
                        postNode.AppendChild(postLinkNode);
                    }
                    catch (SqlNullValueException)
                    {
                        XmlNode postLinkNode = doc.CreateElement("Link");
                        postLinkNode.InnerText = "";
                        postNode.AppendChild(postLinkNode);
                    }

                    XmlNode postImgUrlNode = doc.CreateElement("ImgURL");
                    postImgUrlNode.InnerText = reader.GetString("Post_ImgURL");
                    if (postImgUrlNode.InnerText == "")
                    {
                        postImgUrlNode.InnerText = "http://bportlibrary.org/main/uploads/bulletin-board.jpg";
                    }
                    postNode.AppendChild(postImgUrlNode);

                    XmlNode postDescriptionNode = doc.CreateElement("Description");
                    // TODO - remove this replace once the front-end can handle the ' character.
                    postDescriptionNode.InnerText = reader.GetString("Post_Description")
                        .Replace("&#39;", "")
                        .Replace("\"", "")
                        .Replace("'", "");
                    postNode.AppendChild(postDescriptionNode);

                    XmlNode postZipLocaleNode = doc.CreateElement("ZipLocale");
                    postZipLocaleNode.InnerText = reader.GetString("Post_Zip_Locale");
                    postNode.AppendChild(postZipLocaleNode);

                    XmlNode postCategoryTitleNode = doc.CreateElement("CategoryTitle");
                    postCategoryTitleNode.InnerText = reader.GetString("CategoryTitle");
                    postNode.AppendChild(postCategoryTitleNode);

                    XmlNode postPopularityNode = doc.CreateElement("Popularity");
                    postPopularityNode.InnerText = reader.GetString("PopularityValuePercentage");
                    postNode.AppendChild(postPopularityNode);

                    XmlNode userNode = doc.CreateElement("User");
                    userPostNode.AppendChild(userNode);

                    XmlNode userIdNode = doc.CreateElement("UserID");
                    userIdNode.InnerText = reader.GetString("UserID");
                    userNode.AppendChild(userIdNode);

                    XmlNode userFirstNameNode = doc.CreateElement("UserFirstName");
                    userFirstNameNode.InnerText = reader.GetString("UserFName");
                    userNode.AppendChild(userFirstNameNode);

                    XmlNode userLastNameNode = doc.CreateElement("UserLastName");
                    userLastNameNode.InnerText = reader.GetString("UserLName");
                    userNode.AppendChild(userLastNameNode);

                    XmlNode userBirthDateNode = doc.CreateElement("UserBirthDate");
                    userBirthDateNode.InnerText = reader.GetString("UserBirthDate");
                    userNode.AppendChild(userBirthDateNode);

                    XmlNode userLocaleNode = doc.CreateElement("UserLocale");
                    userLocaleNode.InnerText = reader.GetString("UserLocale");
                    userNode.AppendChild(userLocaleNode);
                }

                // Since we're done with it, close the reader.
                reader.Close();

                // Close the database connection.
                conn.Close();
            }

            return doc;
        }

        public XmlDocument CreateAccount(
            string userId,
            string firstName,
            string lastName,
            string birthday,
            string zip,
            string email,
            string password
            )
        {
            XmlDocument toReturn = new XmlDocument();
            string xml = "<Result>";
            if (userId == "'" ||
                 firstName == "'" ||
                 lastName == "'" ||
                birthday == "'" ||
                zip == "'" ||
                email == "'" ||
                password == "'")
            {
                xml += "<Success>False</Success><Reason>Illegal Data</Reason>";
            }
            else
            {
                try
                {
                    // Will need to convert from string to bytes, use the UTF8Encoding to do that.
                    System.Text.UTF8Encoding encoding = new System.Text.UTF8Encoding();
                    // Will need to hash, use the SHA512 Hash to do that.
                    // Note: SHA512Managed forces the web services to do the
                    // hashing. SHA512CryptoServiceProvider would offload that
                    // work to the O.S. but not all O.S.'s support it. Just let
                    // the web services handle it, it's more portable (and my
                    // development machine, a Windows XP box, doesn't support
                    // it).
                    SHA512Managed hash = new SHA512Managed();
                    // Get the byte representation of the un-hashed password.
                    byte[] passwordBytes = encoding.GetBytes(password);
                    // Hash the bytes.
                    byte[] hashedPasswordBytes = hash.ComputeHash(passwordBytes);
                    // Get the string representation of the hashed bytes.
                    string hashedPassword = encoding.GetString(hashedPasswordBytes);
                    // Open the database connection.
                    using (MySqlConnection conn = this.Open())
                    {

                        // Create the command object.
                        MySqlCommand command = new MySqlCommand();
                        command.Connection = conn;
                        command.CommandText = "Mesh_CreateAccount";
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("?vUserID", userId);
                        command.Parameters.AddWithValue("?vUserFname", firstName);
                        command.Parameters.AddWithValue("?vUserLname", lastName);
                        command.Parameters.AddWithValue("?vBirthDt", birthday);
                        command.Parameters.AddWithValue("?vUserZipLocale", zip);
                        command.Parameters.AddWithValue("?vUserEmail", email);
                        command.Parameters.AddWithValue("?vUserPass", hashedPassword);
                        command.Parameters.Add("?OUserNo", MySqlDbType.Int32);
                        command.Parameters["?OUserNo"].Direction = ParameterDirection.Output;

                        // Execute the command object
                        MySqlDataReader reader = command.ExecuteReader();
                        while (reader.Read())
                        {
                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                xml += reader.GetString(i) + ",";
                            }
                        }

                        // Since we're done with it, close the reader.
                        reader.Close();

                        if (command.Parameters["?OUserNo"].Value.ToString() == "9")
                        {
                            // This means name collision
                            xml += "<Success>False</Success><Reason>User ID Already Taken</Reason>";

                        }
                        else
                        {
                            xml += "<Success>True</Success>";
                        }
                        // Close the database connection.
                        conn.Close();
                    }
                }
                #if DEBUG
                    catch (Exception e)
                    {
                            xml += "<Success>False</Success><Reason>" + e.Message + "</Reason>";
                    }
                #else
                    catch (Exception)
                    {
                            xml += "<Success>False</Success><Reason>Internal Server Error</Reason>";
                    }
                #endif
            }

            xml += "</Result>";
            toReturn.LoadXml(xml);
            return toReturn;
        }

        public XmlDocument Login(string userId, string password)
        {
            XmlDocument toReturn = new XmlDocument();
            string xml = "<Result>";
            bool success = false;
            string sessionId = null;
            string errorText = null;
            if (userId == "'" || password == "'")
            {
                xml += "<Success>False</Success><Reason>Bad Username or Password</Reason>";
            }
            else
            {
                try
                {
                    // Will need to convert from string to bytes, use the UTF8Encoding to do that.
                    System.Text.UTF8Encoding encoding = new System.Text.UTF8Encoding();
                    // Will need to hash, use the SHA512 Hash to do that.
                    // Note: SHA512Managed forces the web services to do the
                    // hashing. SHA512CryptoServiceProvider would offload that
                    // work to the O.S. but not all O.S.'s support it. Just let
                    // the web services handle it, it's more portable (and my
                    // development machine, a Windows XP box, doesn't support
                    // it).
                    SHA512Managed hash = new SHA512Managed();
                    // Get the byte representation of the un-hashed password.
                    byte[] passwordBytes = encoding.GetBytes(password);
                    // Hash the bytes.
                    byte[] hashedPasswordBytes = hash.ComputeHash(passwordBytes);
                    // Get the string representation of the hashed bytes.
                    string hashedPassword = encoding.GetString(hashedPasswordBytes);

                    // Open the database connection.
                    using (MySqlConnection conn = this.Open())
                    {
                        bool gotResponse = false;   // There should be only one row

                        // Mesh_UserLogin(vUserID VARCHAR(45),vPassword VARCHAR(150))
                        // returns a single column: SessionID
                        // Error values are: USER_PASS_INVALID, USER_DOES_NOT_EXIST
                        // Success will be a session identifier.

                        using (MySqlCommand command = new MySqlCommand())
                        {
                            command.Connection = conn;
                            command.CommandText = "Mesh_UserLogin";
                            command.CommandType = CommandType.StoredProcedure;
                            command.Parameters.AddWithValue("?vUserID", userId);
                            command.Parameters.AddWithValue("?vPassword", hashedPassword);

                            using (MySqlDataReader reader = command.ExecuteReader())
                            {
                                while (reader.Read())
                                {
                                    if (gotResponse)
                                    {
                                        success = false;
                                        sessionId = "Internal Server Error";
                                    }
                                    else
                                    {
                                        gotResponse = true;
                                        sessionId = reader.GetString("SessionID");
                                        if (sessionId == "USER_PASS_INVALID" ||
                                            sessionId == "USER_DOES_NOT_EXIST")
                                        {
                                            success = false;
                                            errorText = "Bad Username or Password";
                                        }
                                        else
                                        {
                                            success = true;
                                        }
                                    }
                                }
                                reader.Close();
                            }
                        }
                    }
                }
                catch (MySqlException e)
                {
                    success = false;
                    errorText = e.Message;
                }
                catch (Exception e)
                {
                    // Got an error. Propogate the error message out to the
                    // client side (for now).
                    // TODO - squash this information.
                    success = false;
                    errorText = e.Message;
                }
            }

            if (success)
            {
                xml += "<Success>True</Success><SessionIdentifier>" + sessionId + "</SessionIdentifier>";
            }
            else
            {
                xml += "<Success>False</Success><Reason>" + errorText + "</Reason>";
            }

            xml += "</Result>";
            toReturn.LoadXml(xml);
            return toReturn;
        }

        public XmlDocument MakePost(
            string title,
            string date,
            string link,
            string imageUrl,
            string description,
            string zip,
            string sessionId,
            string categoryTitle)
        {
            XmlDocument toReturn = new XmlDocument();
            string result;
            try
            {
                // Open the database connection.
                using (MySqlConnection conn = this.Open())
                {
                    // Create the command object.
                    MySqlCommand command = new MySqlCommand(
                        "CALL Mesh_CreatePost(" +
                        "@title," +
                        "@date," +
                        "@link," +
                        "@imageUrl," +
                        "@description," +
                        "@zip," +
                        "@sessionId," +
                        "@categoryTitle);", conn);
                    command.Parameters.Add(new MySqlParameter("title", title));
                    command.Parameters.Add(new MySqlParameter("date", date));
                    command.Parameters.Add(new MySqlParameter("link", link));
                    command.Parameters.Add(new MySqlParameter("imageUrl", imageUrl));
                    command.Parameters.Add(new MySqlParameter("description", description));
                    command.Parameters.Add(new MySqlParameter("zip", zip));
                    command.Parameters.Add(new MySqlParameter("sessionId", sessionId));
                    command.Parameters.Add(new MySqlParameter("categoryTitle", categoryTitle));
                    // Execute the command object
                    MySqlDataReader reader = command.ExecuteReader();

                    // Read the results.
                    reader.Read();

                    // Get the resulting post status as result.
                    result = reader.GetString("POST_STATUS");

                    // Since we're done with it, close the reader.
                    reader.Close();

                    // Close the database connection.
                    conn.Close();
                }
            }
            catch (Exception e)
            {
                result = e.Message;
            }
            if (result == "Post_Complete")
            {
                toReturn.LoadXml("<Result><Success>True</Success></Result>");
            }
            else
            {
                toReturn.LoadXml("<Result><Success>False</Success><Reason>" + result + "</Reason></Result>");
            }
            return toReturn;
        }

        public XmlDocument MakeComment(string postId, string sessionId, string comment)
        {
            XmlDocument toReturn = new XmlDocument();
            try
            {
                // Open the database connection.
                using (MySqlConnection conn = this.Open())
                {
                    string postStatus;
                    // Generate the MySqlCommand object.
                    using (MySqlCommand command = new MySqlCommand(
                        "CALL Mesh_CreateComments(" +
                            "@comment," +
                            "@postId," +
                            "@sessionId" +
                        ");", conn))
                    {
                        // Add parameters
                        command.Parameters.Add(new MySqlParameter("comment", comment));
                        command.Parameters.Add(new MySqlParameter("postId", postId));
                        command.Parameters.Add(new MySqlParameter("sessionId", sessionId));

                        // Execute the command.
                        using (MySqlDataReader reader = command.ExecuteReader())
                        {
                            reader.Read();
                            postStatus = reader.GetString("POST_STATUS");
                            reader.Close();
                        }

                        if (postStatus == "PostComment_Complete")
                        {
                            toReturn.LoadXml("<Result><Success>True</Success></Result>");
                        }
                        else if (postStatus == "Session Expired")
                        {
                            toReturn.LoadXml("<Result><Success>False</Success><Reason>Session Expired</Reason></Result>");
                        }
                        else if (postStatus == "Session Not Found")
                        {
                            toReturn.LoadXml("<Result><Success>False</Success><Reason>Session Not Found</Reason></Result>");
                        }
                        else
                        {
#if DEBUG
                            toReturn.LoadXml("<Result><Success>False</Success><Reason>" + postStatus + "</Reason></Result>");
#else
                            toReturn.LoadXml("<Result><Success>False</Success><Reason>Internal Server Error</Reason></Result>");
#endif
                        }
                    }

                    // And we're done. Close the connection.
                    conn.Close();
                }
            }
            #if DEBUG
                catch (Exception e)
                {
                    // If anything throws an exception, handle it here.
                    toReturn.LoadXml("<Result><Success>False</Success><Reason>" + e.Message + "</Reason></Result>");
                }
            #else
                catch (Exception)
                {
                    toReturn.LoadXml("<Result><Success>False</Success><Reason>Internal Server Error</Reason></Result>");
                }
            #endif

            return toReturn;
        }

        public XmlDocument GetComments(string postId)
        {
            XmlDocument doc = new XmlDocument();
            XmlNode userPostsNode = doc.CreateElement("Comments");
            doc.AppendChild(userPostsNode);
            try
            {
                // Open the database connection.
                using (MySqlConnection conn = this.Open())
                {
                    // Generate the MySqlCommand object.
                    using (MySqlCommand command = new MySqlCommand(
                        "CALL Mesh_GetUserCommentsPost(@postId);", conn))
                    {
                        // Add parameters
                        command.Parameters.Add(new MySqlParameter("postId", postId));

                        // Execute the command.
                        using (MySqlDataReader reader = command.ExecuteReader())
                        {
                            // Read all of the comments returned
                            while (reader.Read())
                            {
                                XmlNode commentNode = doc.CreateElement("Comment");
                                userPostsNode.AppendChild(commentNode);

                                XmlNode userIdNode = doc.CreateElement("UserId");
                                userIdNode.InnerText = reader.GetString("UserID");
                                commentNode.AppendChild(userIdNode);

                                XmlNode commentTextNode = doc.CreateElement("Text");
                                commentTextNode.InnerText = reader.GetString("Comment_Text");
                                commentNode.AppendChild(commentTextNode);
                            }
                            // And we're done with the reader, close it.
                            reader.Close();
                        }
                    }

                    // And we're done with the connection, close it.
                    conn.Close();
                }
            }
#if DEBUG
                catch (Exception e)
                {
                    // If anything throws an exception, handle it here.
                    doc = new XmlDocument();
                    XmlNode resultNode = doc.CreateElement("Result");
                    doc.AppendChild(resultNode);

                    XmlNode successNode = doc.CreateElement("Success");
                    successNode.InnerText = "False";
                    resultNode.AppendChild(successNode);

                    XmlNode reasonNode = doc.CreateElement("Reason");
                    reasonNode.InnerText = e.Message;
                    resultNode.AppendChild(reasonNode);
                }
#else
                catch (Exception)
                {
                    doc = new XmlDocument();
                    XmlNode resultNode = doc.CreateElement("Result");
                    doc.AppendChild(resultNode);

                    XmlNode successNode = doc.CreateElement("Success");
                    successNode.InnerText = "False";
                    resultNode.AppendChild(successNode);

                    XmlNode reasonNode = doc.CreateElement("Reason");
                    reasonNode.InnerText = "Internal Server Error";
                    resultNode.AppendChild(reasonNode);
                }
#endif

            return doc;
        }

        public XmlDocument Logout(string userId)
        {
            XmlDocument toReturn = new XmlDocument();
            try
            {
                // Open the database connection.
                using (MySqlConnection conn = this.Open())
                {
                    // Generate the MySqlCommand object.
                    using (MySqlCommand command = new MySqlCommand(
                        "DELETE FROM Mesh_Session WHERE SessionID = @userId;", conn))
                    {
                        // Add parameters
                        command.Parameters.Add(new MySqlParameter("userId", userId));
                        command.ExecuteNonQuery();
                    }
                    conn.Close();
                }
                toReturn.LoadXml("<Result><Success>True</Success></Result>");
            }
#if DEBUG
                catch (Exception e)
                {
                    // If anything throws an exception, handle it here.
                    toReturn.LoadXml("<Result><Success>False</Success><Reason>" + e.Message + "</Reason></Result>");
                }
#else
                catch (Exception)
                {
                    toReturn.LoadXml("<Result><Success>False</Success><Reason>Internal Server Error</Reason></Result>");
                }
#endif
            return toReturn;
        }

        public XmlDocument RatePost(string sessionId, string postId, string rating)
        {
            XmlDocument toReturn = new XmlDocument();
            try
            {
                // Open the database connection.
                using (MySqlConnection conn = this.Open())
                {
                    // Generate the MySqlCommand object.
                    using (MySqlCommand command = new MySqlCommand(
                        "CALL Mesh_DeterminePostPop(" +
                            "@vUserSession," +
                            "@vPostID," +
                            "@vPopValue" +
                        ");", conn))
                    {
                        bool execute = true;
                        // Add parameters
                        command.Parameters.Add(new MySqlParameter("vUserSession", sessionId));
                        command.Parameters.Add(new MySqlParameter("vPostID", postId));
                        if (rating == "+1")
                        {
                            command.Parameters.Add(new MySqlParameter("vPopValue", "0.2"));
                        }
                        else if (rating == "-1")
                        {
                            command.Parameters.Add(new MySqlParameter("vPopValue", "0.1"));
                        }
                        else if (rating == "0")
                        {
                            command.Parameters.Add(new MySqlParameter("vPopValue", "0"));
                        }
                        else
                        {
                            execute = false;
                        }

                        if (execute)
                        {
                            using (MySqlDataReader reader = command.ExecuteReader())
                            {
                                if (reader.Read())
                                {
                                    if (reader.GetString(0) == "PostPopularity_Updated")
                                    {
                                        toReturn.LoadXml("<Result><Success>True</Success></Result>");
                                    }
                                    else
                                    {
#if DEBUG
                                        toReturn.LoadXml("<Result><Success>False</Success><Reason>" + reader.GetString(0) + "</Reason></Result>");
#else
                                        toReturn.LoadXml("<Result><Success>False</Success><Reason>Internal Server Error</Reason></Result>");
#endif
                                    }
                                }
                                else
                                {
#if DEBUG
                                    toReturn.LoadXml("<Result><Success>False</Success><Reason>Could not issue query</Reason></Result>");
#else
                                    toReturn.LoadXml("<Result><Success>False</Success><Reason>Internal Server Error</Reason></Result>");
#endif
                                }
                            }
                        }
                        else
                        {
#if DEBUG
                            toReturn.LoadXml("<Result><Success>False</Success><Reason>Bad rating value</Reason></Result>");
#else
                            toReturn.LoadXml("<Result><Success>False</Success><Reason>Internal Server Error</Reason></Result>");
#endif
                        }
                    }
                    conn.Close();
                }
            }

#if DEBUG
                catch (Exception e)
                {
                    // If anything throws an exception, handle it here.
                    toReturn.LoadXml("<Result><Success>False</Success><Reason>" + e.Message + "</Reason></Result>");
                }
#else
                catch (Exception)
                {
                    toReturn.LoadXml("<Result><Success>False</Success><Reason>Internal Server Error</Reason></Result>");
                }
#endif
            return toReturn;
        }

        public XmlDocument GetUserRatingForPost(string sessionId, string postId)
        {
            bool success = true;
#if DEBUG
            string reason = null;
#endif
            string rating = null;
            try
            {
                // Open the database connection.
                using (MySqlConnection conn = this.Open())
                {
                    // Generate the MySqlCommand object.
                    using (MySqlCommand command = new MySqlCommand(
                        "CALL Mesh_GetUserRating(" +
                            "@vUserSession," +
                            "@vPostID" +
                        ");", conn))
                    {
                        // Add parameters
                        command.Parameters.Add(new MySqlParameter("vUserSession", sessionId));
                        command.Parameters.Add(new MySqlParameter("vPostID", postId));

                        using (MySqlDataReader reader = command.ExecuteReader())
                        {
                            if (reader.Read())
                            {
                                rating = reader.GetString("POPULARITY_VALUE");
                            }
                            else
                            {
                                success = false;
#if DEBUG
                                reason = "Could not issue query";
#endif
                            }
                            reader.Close();
                        }
                    }
                    conn.Close();
                }
            }
#if DEBUG
                catch (Exception e)
                {
                    success = false;
                    reason = e.Message;
                }
#else
                catch (Exception)
                {
                    success = false;
                }
#endif
            XmlDocument toReturn = new XmlDocument();
            XmlNode resultNode = toReturn.CreateElement("Result");
            toReturn.AppendChild(resultNode);
            XmlNode successNode = toReturn.CreateElement("Success");

            if (success)
            {   
                successNode.InnerText = "True";
            }
            else
            {
                successNode.InnerText = "False";
            }
            resultNode.AppendChild(successNode);

            if (success)
            {
                XmlNode ratingNode = toReturn.CreateElement("Rating");
                ratingNode.InnerText = rating;
                resultNode.AppendChild(ratingNode);
            }
            else
            {
                XmlNode reasonNode = toReturn.CreateElement("Reason");
#if DEBUG
                reasonNode.InnerText = reason;
#else
                reasonNode.InnerText = "Internal Server Error";
#endif
                resultNode.AppendChild(reasonNode);
            }
            return toReturn;
        }

        private string GetUserNoFromUserId(string userId, MySqlConnection conn)
        {
            string toReturn = null;
            using (MySqlCommand command = new MySqlCommand("SELECT UserNo from Mesh_User WHERE UserID=@userId", conn))
            {
                command.Parameters.Add(new MySqlParameter("UserID", userId));
                using (MySqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        toReturn = reader.GetString("UserNo");
                    }
                    else
                    {
                        toReturn = "User Not Found.";
                    }
                    reader.Close();
                }
            }
            return toReturn;
        }

        private string GetUserNoFromSessionId(string sessionId, MySqlConnection conn)
        {
            string toReturn = null;
            using (MySqlCommand command = new MySqlCommand("SELECT UserNo from Mesh_Session WHERE SessionID=@sessionId", conn))
            {
                command.Parameters.Add(new MySqlParameter("sessionId", sessionId));
                using (MySqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        toReturn = reader.GetString("UserNo");
                    }
                    else
                    {
                        toReturn = "User Not Found.";
                    }
                    reader.Close();
                }
            }
            return toReturn;
        }

        private void DropUserSession(string userNo, MySqlConnection conn)
        {
            if (userNo != "User Not Found.")
            {
                using (MySqlCommand command = new MySqlCommand())
                {
                    command.Connection = conn;
                    command.CommandText = "DELETE FROM Mesh_Session WHERE UserNo = @UserNo;";
                    command.Parameters.AddWithValue("UserNo", userNo);
                    command.ExecuteNonQuery();
                }
            }
        }

        public void DropPostRatings(string testPostId)
        {
            using (MySqlConnection conn = this.Open())
            {
                using (MySqlCommand command = new MySqlCommand("DELETE FROM Mesh_Post_Popularity WHERE Post_ID = @testPostId;", conn))
                {
                    command.Parameters.Add(new MySqlParameter("testPostId", testPostId));
                    command.ExecuteNonQuery();
                }

                conn.Close();
            }
        }
        public void DropTestPostComments(string testPostId)
        {
            // Open the database connection.
            using (MySqlConnection conn = this.Open())
            {
                using (MySqlCommand command = new MySqlCommand("DELETE FROM Mesh_Post_Comments WHERE Post_ID = @testPostId;", conn))
                {
                    command.Parameters.Add(new MySqlParameter("testPostId", testPostId));
                    command.ExecuteNonQuery();
                }

                conn.Close();
            }
        }

        public void DropAccount(string userId)
        {
            // Open the database connection.
            using (MySqlConnection conn = this.Open())
            {
                // Drop the user's session
                DropUserSession(GetUserNoFromUserId(userId, conn), conn);
                // Create the command object.
                MySqlCommand command = new MySqlCommand("CALL Mesh_DropAccount(@userId);", conn);
                command.Parameters.Add(new MySqlParameter("userId", userId));
                // Execute the command object
                command.ExecuteNonQuery();
                // Close the database connection.
                conn.Close();
            }
        }

        public void DropAllPosts(string zip)
        {
            // Open the database connection.
            using (MySqlConnection conn = this.Open())
            {
                // Create the command object.
                MySqlCommand command = new MySqlCommand("delete from Mesh_Post where Post_Zip_Locale = @zip;", conn);
                command.Parameters.AddWithValue("zip", zip);
                // Execute the command object
                command.ExecuteNonQuery();
                // Close the database connection.
                conn.Close();
            }
        }

        // Open the connection.
        private MySqlConnection Open()
        {
            // Compute the connection string.
            // The "server" is the database address. If the DbAccess object was
            //     created with inDevEnvironment set to true, this will be
            //     localhost, otherwise it will be the actual production
            //     database address.
            // The user is the username for the database connection.
            // The "database" is the database schema to be accessed.
            // The password is the password for the database connection.
            string connectionString = "server=" + this.dbAddress + ";" +
                                      "user=" + this.dbUserName + ";" +
                                      "database=" + this.dbDatabase + ";" +
                                      "password=" + this.dbPassword + ";";
            // Create the connection.
            MySqlConnection conn = new MySqlConnection(connectionString);
            conn.Open();
            return conn;
        }

        private XmlElement appendChildWithData(ref XmlDocument xmlDoc, string name, string data)
        {
            XmlElement childNode = xmlDoc.CreateElement(name);
            childNode.InnerText = data;
            xmlDoc.DocumentElement.AppendChild(childNode);

            return childNode;
        }


        // Destructor, just to make sure that the connection is closed when our
        // instance is destroyed.
        ~DbAccess()
        {
        }
    }
}